package com.example.lastwork;

import android.util.Log;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDao {
    //查询用户的方法
    private String TAG = "UserDAO";

    public static String select(String name, String what) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        String pwd = null;
        DBUtils.release(conn, state, rs);
        try {
            conn = DBUtils.getConnect();
            state = conn.createStatement();
            String sql = "SELECT * FROM user WHERE id ='" + name+"'";
            rs = state.executeQuery(sql);
            while (rs.next()) {
                pwd = rs.getString(what);
                System.out.println(rs.getString("id") + " " + rs.getString("password"));//就是输出第一列和第二列的值
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
        return pwd;
    }

    public static void add(String id, String password, String name) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        try {
            conn = DBUtils.getConnect();
            String sql = "INSERT INTO user (id,name,password) VALUES(?,?,?)";
            PreparedStatement pstm = conn.prepareStatement(sql);
            pstm.setString(1, id);
            pstm.setString(2, name);
            pstm.setString(3, password);
            pstm.executeUpdate();
            System.out.println("id:" + id + ",name:" + name + ",password:" + password);
            conn.close();
            pstm.close();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
    }

    public List<Map<String, String>> getData(List<Map<String,String>> data_list) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        try{
            conn = DBUtils.getConnect();
            Log.i(TAG,"连接成功");
            state = conn.createStatement();
            String sql = "SELECT DISTINCT * FROM share a,user b  WHERE a.id = b.id";
            rs = state.executeQuery(sql);
            rs.last();
            int count=rs.getRow();
            String[] name = new String[count];
            Log.i(TAG,"得到："+name.length);
            String[] title = new String[count];
            Log.i(TAG,"得到："+title.length);
            String[] id = new String[count];
            Log.i(TAG,"得到："+title.length);
            int[] num = new int[count];
            int j = 0;
            rs = state.executeQuery(sql);
            if (rs != null) {
                while (rs.next()) {
                    title[j] = rs.getString("标题");
                    Log.i(TAG,"得到"+title[j]);
                    name[j] = rs.getString("name");
                    Log.i(TAG, "得到" + name[j]);
                    id[j] = rs.getString("id");
                    Log.i(TAG, "得到" + id[j]);
                    num[j] = rs.getInt("number");
                    j++;
                }
                rs.close();
            }
            state.close();
            conn.close();
            for (int i = 0; i < name.length; i++) {
                Map<String, String> map = new HashMap<String, String>();
                map.put("name", name[i]);
                map.put("title", title[i]);
                map.put("id",id[i]);
                map.put("num",String.valueOf(num[i]));
                data_list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
        return data_list;
    }

    public static void addcon(String title, String content, String id) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        try {
            conn = DBUtils.getConnect();
            String sql = "INSERT INTO share (标题,内容,id) VALUES(?,?,?)";
            PreparedStatement pstm = conn.prepareStatement(sql);
            pstm.setString(1, title);
            pstm.setString(2, content);
            pstm.setString(3, id);
            pstm.executeUpdate();
            System.out.println("title:" + title + ",content:" + content + ",id:" + id);
            conn.close();
            pstm.close();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
    }

    public List<Map<String, String>> getlove(List<Map<String,String>> data_list,String id) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        try{
            conn = DBUtils.getConnect();
            Log.i(TAG,"连接成功");
            state = conn.createStatement();
            String sql = "SELECT DISTINCT * FROM love,user WHERE id1 = "+id+" and id2 = id";
            rs = state.executeQuery(sql);
            rs.last();
            int count=rs.getRow();
            String[] name = new String[count];
            Log.i(TAG,"得到："+name.length);
            String[] ids = new String[count];
            Log.i(TAG,"得到："+ids.length);
            int j = 0;
            rs = state.executeQuery(sql);
            if (rs != null) {
                while (rs.next()) {
                    name[j] = rs.getString("name");
                    Log.i(TAG, "得到" + name[j]);
                    ids[j] = rs.getString("id");
                    Log.i(TAG, "得到" + ids[j]);
                    j++;
                }
                rs.close();
            }
            state.close();
            conn.close();
            for (int i = 0; i < name.length; i++) {
                Map<String, String> map = new HashMap<String, String>();
                map.put("name", name[i]);
                map.put("id",ids[i]);
                data_list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
        return data_list;
    }

    public static String getuser(String name) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        String name1 = null;
        try {
            conn = DBUtils.getConnect();
            state = conn.createStatement();
            String sql = "SELECT * FROM user WHERE id ='"+name+"'";
            rs = state.executeQuery(sql);
            rs.next();
            name1 = rs.getString("name");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
        return name1;
    }
    public List<Map<String, String>> getpersonData(List<Map<String,String>> data_list,String s_id) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        try{
            conn = DBUtils.getConnect();
            Log.i(TAG,"连接成功");
            state = conn.createStatement();
            String sql = "SELECT DISTINCT * FROM share a,user b  WHERE a.id = b.id and a.id ='"+s_id+"'";
            rs = state.executeQuery(sql);
            rs.last();
            int count=rs.getRow();
            String[] name = new String[count];
            Log.i(TAG,"得到："+name.length);
            String[] title = new String[count];
            Log.i(TAG,"得到："+title.length);
            String[] id = new String[count];
            Log.i(TAG,"得到："+title.length);
            int[] num = new int[count];
            int j = 0;
            rs = state.executeQuery(sql);
            if (rs != null) {
                while (rs.next()) {
                    title[j] = rs.getString("标题");
                    Log.i(TAG,"得到"+title[j]);
                    name[j] = rs.getString("name");
                    Log.i(TAG, "得到" + name[j]);
                    id[j] = rs.getString("id");
                    Log.i(TAG, "得到" + id[j]);
                    num[j] = rs.getInt("number");
                    j++;
                }
                rs.close();
            }
            state.close();
            conn.close();
            for (int i = 0; i < name.length; i++) {
                Map<String, String> map = new HashMap<String, String>();
                map.put("name", name[i]);
                map.put("title", title[i]);
                map.put("id",id[i]);
                map.put("num",String.valueOf(num[i]));
                data_list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
        return data_list;
    }
    public List<Map<String, String>> getdetailData(List<Map<String,String>> data_list,int number) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        try{
            conn = DBUtils.getConnect();
            Log.i(TAG,"连接成功");
            state = conn.createStatement();
            String sql = "SELECT DISTINCT * FROM share a,user b  WHERE a.id = b.id and number = '"+number+"'";
            rs = state.executeQuery(sql);
            rs.last();
            int count=rs.getRow();
            String[] name = new String[count];
            Log.i(TAG,"得到："+name.length);
            String[] title = new String[count];
            Log.i(TAG,"得到："+title.length);
            String[] id = new String[count];
            Log.i(TAG,"得到："+title.length);
            String[] content = new String[count];
            int j = 0;
            rs = state.executeQuery(sql);
            if (rs != null) {
                while (rs.next()) {
                    title[j] = rs.getString("标题");
                    Log.i(TAG,"得到"+title[j]);
                    name[j] = rs.getString("name");
                    Log.i(TAG, "得到" + name[j]);
                    id[j] = rs.getString("id");
                    Log.i(TAG, "得到" + id[j]);
                    content[j] = rs.getString("内容");
                    j++;
                }
                rs.close();
            }
            state.close();
            conn.close();
            for (int i = 0; i < name.length; i++) {
                Map<String, String> map = new HashMap<String, String>();
                map.put("name", name[i]);
                map.put("title", title[i]);
                map.put("id",id[i]);
                map.put("content",content[i]);
                data_list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
        return data_list;
    }

    public List<Map<String, String>> gettaolunData(List<Map<String,String>> data_list,int number) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        try{
            conn = DBUtils.getConnect();
            Log.i(TAG,"连接成功");
            state = conn.createStatement();
            String sql = "SELECT DISTINCT * FROM opinion  WHERE number = '"+number+"'";
            rs = state.executeQuery(sql);
            rs.last();
            int count=rs.getRow();
            String[] name = new String[count];
            Log.i(TAG,"得到："+name.length);
            String[] chat = new String[count];
            Log.i(TAG,"得到："+chat.length);
            int j = 0;
            rs = state.executeQuery(sql);
            if (rs != null) {
                while (rs.next()) {
                    chat[j] = rs.getString("chat");
                    Log.i(TAG,"得到"+chat[j]);
                    name[j] = rs.getString("name");
                    Log.i(TAG, "得到" + name[j]);
                    j++;
                }
                rs.close();
            }
            state.close();
            conn.close();
            for (int i = 0; i < name.length; i++) {
                Map<String, String> map = new HashMap<String, String>();
                map.put("name", name[i]);
                map.put("chat", chat[i]);
                data_list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
        return data_list;
    }

    public static void addtaolun(String id, String name, String chat,int num) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        try {
            conn = DBUtils.getConnect();
            String sql = "INSERT INTO opinion (id,name,chat,number) VALUES(?,?,?,?)";
            PreparedStatement pstm = conn.prepareStatement(sql);
            pstm.setString(1, id);
            pstm.setString(2, name);
            pstm.setString(3, chat);
            pstm.setInt(4, num);
            pstm.executeUpdate();
            System.out.println("id:" + id + ",name:" + name + ",chat:" + chat+",num:"+num);
            conn.close();
            pstm.close();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
    }

    public static void addlover(String id1, String id2) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        try {
            conn = DBUtils.getConnect();
            String sql = "INSERT INTO opinion (id1,id2) VALUES(?,?)";
            PreparedStatement pstm = conn.prepareStatement(sql);
            pstm.setString(1, id1);
            pstm.setString(2, id2);
            pstm.executeUpdate();
            System.out.println("id:" + id1+ ",name:" + id2);
            conn.close();
            pstm.close();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.release(conn, state, rs);
        }
    }

    public static String[] selectlover(String id) throws Exception {
        Connection conn = null;
        Statement state = null;
        ResultSet rs = null;
        DBUtils.release(conn, state, rs);
            conn = DBUtils.getConnect();
            state = conn.createStatement();
            String sql = "SELECT * FROM love WHERE id1 ='" + id+"'";
            rs = state.executeQuery(sql);
            rs.last();
            int count=rs.getRow();
            String id2[] = new String[count];
            int i = 0;
            while (rs.next()) {
                id2[i] = rs.getString("id2");
                System.out.println(rs.getString("id2") + " ");//就是输出第一列和第二列的值
                i++;
            }
            DBUtils.release(conn, state, rs);

        return new String[]{id2[i]};
    }
}


